"""数据库初始化模块

负责数据库连接测试、表结构检查和自动创建
在项目启动时确保数据库环境正确配置
使用MySQL数据库，支持自动建表和结构验证
"""

import logging
import pymysql
from typing import Dict, List, Tuple, Optional
from app.config import settings

# 配置日志
logger = logging.getLogger(__name__)


class DatabaseInitializer:
    """数据库初始化器
    
    负责数据库连接测试、表检查和自动创建
    """
    
    def __init__(self):
        """初始化数据库连接参数"""
        self.host = settings.db_host
        self.port = settings.db_port
        self.user = settings.db_user
        self.password = settings.db_password
        self.database = settings.db_name
        
        logger.info(f"数据库初始化器已创建 - 主机: {self.host}:{self.port}, 数据库: {self.database}")
    
    def test_connection(self) -> bool:
        """测试数据库连接
        
        Returns:
            bool: 连接成功返回True，失败返回False
        """
        try:
            logger.info("开始测试数据库连接...")
            
            # 首先测试服务器连接（不指定数据库）
            connection = pymysql.connect(
                host=self.host,
                port=self.port,
                user=self.user,
                password=self.password,
                charset='utf8mb4'
            )
            
            with connection.cursor() as cursor:
                cursor.execute("SELECT VERSION()")
                version = cursor.fetchone()
                logger.info(f"MySQL服务器连接成功，版本: {version[0]}")
            
            connection.close()
            
            # 测试目标数据库连接
            return self._test_database_connection()
            
        except Exception as e:
            logger.error(f"数据库连接测试失败: {str(e)}")
            return False
    
    def _test_database_connection(self) -> bool:
        """测试目标数据库连接"""
        try:
            connection = pymysql.connect(
                host=self.host,
                port=self.port,
                user=self.user,
                password=self.password,
                database=self.database,
                charset='utf8mb4'
            )
            
            with connection.cursor() as cursor:
                cursor.execute("SELECT DATABASE()")
                db_name = cursor.fetchone()
                logger.info(f"目标数据库连接成功: {db_name[0]}")
            
            connection.close()
            return True
            
        except pymysql.err.OperationalError as e:
            if "Unknown database" in str(e):
                logger.warning(f"数据库 '{self.database}' 不存在，尝试创建...")
                return self._create_database()
            else:
                logger.error(f"数据库连接失败: {str(e)}")
                return False
        except Exception as e:
            logger.error(f"数据库连接测试异常: {str(e)}")
            return False
    
    def _create_database(self) -> bool:
        """创建数据库"""
        try:
            connection = pymysql.connect(
                host=self.host,
                port=self.port,
                user=self.user,
                password=self.password,
                charset='utf8mb4'
            )
            
            with connection.cursor() as cursor:
                create_db_sql = f"""
                CREATE DATABASE IF NOT EXISTS `{self.database}` 
                CHARACTER SET utf8mb4 
                COLLATE utf8mb4_unicode_ci
                """
                cursor.execute(create_db_sql)
                logger.info(f"数据库 '{self.database}' 创建成功")
            
            connection.close()
            return True
            
        except Exception as e:
            logger.error(f"创建数据库失败: {str(e)}")
            return False
    
    def check_and_create_tables(self) -> bool:
        """检查并创建所需的数据表
        
        Returns:
            bool: 所有表检查/创建成功返回True
        """
        try:
            logger.info("开始检查数据库表结构...")
            
            connection = pymysql.connect(
                host=self.host,
                port=self.port,
                user=self.user,
                password=self.password,
                database=self.database,
                charset='utf8mb4'
            )
            
            success = True
            
            # 检查并创建brain_wave表
            if not self._table_exists(connection, 'brain_wave'):
                logger.info("brain_wave表不存在，开始创建...")
                success &= self._create_brain_wave_table(connection)
            else:
                logger.info("brain_wave表已存在")
            
            # 检查并创建sleep_analysis_sessions表
            if not self._table_exists(connection, 'sleep_analysis_sessions'):
                logger.info("sleep_analysis_sessions表不存在，开始创建...")
                success &= self._create_sleep_analysis_sessions_table(connection)
            else:
                logger.info("sleep_analysis_sessions表已存在")
            
            connection.close()
            
            if success:
                logger.info("所有数据库表检查/创建完成")
            else:
                logger.error("部分数据库表创建失败")
            
            return success
            
        except Exception as e:
            logger.error(f"检查数据库表时发生异常: {str(e)}")
            return False
    
    def _table_exists(self, connection, table_name: str) -> bool:
        """检查表是否存在"""
        try:
            with connection.cursor() as cursor:
                cursor.execute("""
                    SELECT COUNT(*) 
                    FROM information_schema.tables 
                    WHERE table_schema = %s AND table_name = %s
                """, (self.database, table_name))
                
                result = cursor.fetchone()
                return result[0] > 0
                
        except Exception as e:
            logger.error(f"检查表 {table_name} 是否存在时发生错误: {str(e)}")
            return False
    
    def _create_brain_wave_table(self, connection) -> bool:
        """创建brain_wave表"""
        try:
            create_sql = """
            CREATE TABLE `brain_wave` (
              `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
              `room_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '所属房间ID',
              `user_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户ID（可选）',
              `start_time` datetime NOT NULL COMMENT '会话开始时间',
              `end_time` datetime NOT NULL COMMENT '会话结束时间',
              `total_seconds` int(11) NOT NULL COMMENT '会话时长（秒）',
              `eeg_wave_data` json DEFAULT NULL COMMENT 'EEG原始波形数据',
              `delta_data` json DEFAULT NULL COMMENT 'Delta波段数据(0.5-4Hz)',
              `theta_data` json DEFAULT NULL COMMENT 'Theta波段数据(4-8Hz)',
              `low_alpha_data` json DEFAULT NULL COMMENT '低Alpha波段数据(8-10Hz)',
              `high_alpha_data` json DEFAULT NULL COMMENT '高Alpha波段数据(10-12Hz)',
              `low_beta_data` json DEFAULT NULL COMMENT '低Beta波段数据(12-20Hz)',
              `high_beta_data` json DEFAULT NULL COMMENT '高Beta波段数据(20-30Hz)',
              `low_gamma_data` json DEFAULT NULL COMMENT '低Gamma波段数据(30-40Hz)',
              `mid_gamma_data` json DEFAULT NULL COMMENT '中Gamma波段数据(40-50Hz)',
              `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录更新时间',
              PRIMARY KEY (`id`),
              KEY `idx_room_id` (`room_id`),
              KEY `idx_user_id` (`user_id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=1000012 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='睡眠会话数据表'
            """
            
            with connection.cursor() as cursor:
                cursor.execute(create_sql)
                connection.commit()
                logger.info("brain_wave表创建成功")
                return True
                
        except Exception as e:
            logger.error(f"创建brain_wave表失败: {str(e)}")
            return False
    
    def _create_sleep_analysis_sessions_table(self, connection) -> bool:
        """创建sleep_analysis_sessions表"""
        try:
            create_sql = """
            CREATE TABLE `sleep_analysis_sessions` (
              `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
              `session_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '会话ID，业务主键',
              `room_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '房间ID',
              `user_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'default_user' COMMENT '用户ID',
              `session_start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '会话开始时间',
              `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
              `session_duration_seconds` int(11) DEFAULT '0' COMMENT '会话总时长（秒）',
              `total_data_points` int(11) DEFAULT '0' COMMENT '总数据点数量',
              `first_data_timestamp` double DEFAULT NULL COMMENT '第一个数据点时间戳',
              `last_data_timestamp` double DEFAULT NULL COMMENT '最后一个数据点时间戳',
              `frequency_analysis_data` json NOT NULL COMMENT '频域比例分析结果数组，包含时间戳和5个频域数据',
              `session_status` enum('active','completed','interrupted') COLLATE utf8mb4_unicode_ci DEFAULT 'active' COMMENT '会话状态',
              `average_confidence` decimal(5,4) DEFAULT '0.0000' COMMENT '平均置信度',
              `dominant_sleep_state` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT 'unknown' COMMENT '主要睡眠状态',
              `signal_quality_score` decimal(5,4) DEFAULT '0.0000' COMMENT '信号质量评分',
              `data_completeness_ratio` decimal(5,4) DEFAULT '0.0000' COMMENT '数据完整性比例',
              PRIMARY KEY (`id`),
              UNIQUE KEY `session_id` (`session_id`),
              KEY `idx_session_id` (`session_id`),
              KEY `idx_room_id` (`room_id`),
              KEY `idx_user_id` (`user_id`),
              KEY `idx_session_start_time` (`session_start_time`),
              KEY `idx_last_update_time` (`last_update_time`),
              KEY `idx_session_status` (`session_status`)
            ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='睡眠分析会话表-频域比例累积存储'
            """
            
            with connection.cursor() as cursor:
                cursor.execute(create_sql)
                connection.commit()
                logger.info("sleep_analysis_sessions表创建成功")
                return True
                
        except Exception as e:
            logger.error(f"创建sleep_analysis_sessions表失败: {str(e)}")
            return False
    
    def initialize_database(self) -> bool:
        """完整的数据库初始化流程
        
        Returns:
            bool: 初始化成功返回True
        """
        logger.info("开始数据库初始化流程...")
        
        # 1. 测试数据库连接
        if not self.test_connection():
            logger.error("数据库连接测试失败，初始化中止")
            return False
        
        # 2. 检查并创建表
        if not self.check_and_create_tables():
            logger.error("数据库表检查/创建失败，初始化中止")
            return False
        
        logger.info("数据库初始化完成！")
        return True


# 创建全局初始化器实例
db_initializer = DatabaseInitializer()


def init_database_on_startup() -> bool:
    """启动时的数据库初始化函数
    
    供main.py调用的便捷函数
    
    Returns:
        bool: 初始化成功返回True
    """
    return db_initializer.initialize_database()